import time
import datetime as dt
import pandas as pd
import numpy as np
from numpy import NaN
import mysqlaction as actdb
# import pymysql as pmq
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.Qt import QRegExpValidator, QRegExp
import sys
import os
from PIL import Image
from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg as matcanvas
from matplotlib.figure import Figure as matfig
import matplotlib
import matplotlib.dates as mdates
from pandas.plotting import register_matplotlib_converters
register_matplotlib_converters()
matplotlib.use('Qt5Agg')
matplotlib.rcParams['font.sans-serif'] = ['SimHei']
matplotlib.rcParams['axes.unicode_minus'] = False

############################## widgets  ###############################


def get_near_index(list0, value):
    leng = len(list0)
    min0 = min(list0)
    max0 = max(list0)
    if value < min0 or value > max0:
        return

    l1 = [0]*leng
    for i in range(leng):
        l1[i] = abs(list0[i]-float(value))
    return l1.index(min(l1))


class columnlist(QtWidgets.QWidget):
    def __init__(self):
        super().__init__()

    def update(self, choice, data):
        self.lay = QtWidgets.QVBoxLayout()
        row = len(data)
        col = len(data[0])
        row = [row, 10][row > 10]
        self.boxs = [QtWidgets.QComboBox()]*col
        self.table = QtWidgets.QTableWidget(row+1, col)
        self.lay.addWidget(self.table)
        self.setLayout(self.lay)

        for j in range(col):
            self.boxs[j].addItems(choice)
            self.table.setCellWidget(0, j, self.boxs[j])
            for i in range(1, row+1):
                stri = QtWidgets.QTableWidgetItem(str(data[i-1, j]))
                self.table.setItem(i, j, stri)


'''
class modeltemplate(QtCore.QAbstractItemModel):
    def __init__(self,data=None):
        super().__init__()
        self.update(data)
    def update(self,data=None):
        pass
    def index(self):
        pass
    def parent(self):
        pass
    def rowCount(self,parent=QtCore.QModelIndex()):
        pass

    def columnCount(self,parent=QtCore.QModelIndex()):
        pass
    def data(self,index,role=QtCore.Qt.DisplayRole):
        row=index.row()
        col=index.column()
        if role==QtCore.Qt.DisplayRole:
            pass
        elif role==QtCore.Qt.FontRole:
            pass
        else:
            pass
'''
# ===============matfig================


class curvecanvas(matcanvas):
    def __init__(self, parent=None, width=10, height=5, dpi=100):
        self.fig = matfig(figsize=(width*0.9, height), dpi=dpi)
        matcanvas.__init__(self, self.fig)
        self.data = None
        self.xcol = ''
        # self.setParent(parent)
    '''
    def setxlim(self,xlim):
        if isinstance(self.data,None):
            return
        self.subplots[0].set_xlim(xlim[0],xlim[1])

        for dataname in self.data:

            self.data[dataname]
            [(self.data[self.xcol].__ge__(xlim[0]))&(self.data[self.xcol].__le__(xlim[1]))]
    '''

    def plotdata(self, data, xcol, xlim, **kwargs):  # data is a list of dataframe
        # frame process
        def make_patch_spines_invisible(ax):
            ax.set_frame_on(False)
            ax.patch.set_visible(False)
            ax.set_xticks([])
            ax.set_yticks([])
            for sp in ax.spines.values():
                sp.set_visible(False)
        self.xcol = xcol
        self.data = data
        self.xvl=[]
        self.anno=[]
        leng = len(data)
        self.subplots = self.fig.subplots(leng, 1, sharex='all')
        for subp in self.subplots:
            make_patch_spines_invisible(subp)
        self.subplots[0].set_xlim(xlim[0], xlim[1])

        self.subplotd = {}
        self.axes = {}
        self.curves = {}

        isubplot = 0

        basex = []
        for dataname in data:
            basex = basex+data[dataname][xcol].tolist()
        basex = pd.DataFrame(basex, columns=['x'])
        basex.sort_values('x', inplace=True)
        basex['y'] = 0

        rightrc=1
        offsetrc=70/self.get_width_height()[0]
        for dataname in data:
            z = len(kwargs['style'][dataname])+1
            self.subplotd[dataname] = self.subplots[isubplot]
            self.subplotd[dataname].set_zorder(z)
            isubplot = isubplot+1

            self.subplotd[dataname].set_title(dataname)
            self.subplotd[dataname].set_frame_on(False)
            [curve] = self.subplotd[dataname].plot(
                basex['x'], basex['y'], alpha=0)  # base curve

            self.axes[dataname] = {}  # a dict contain curves
            self.curves[dataname] = {}

            # plot the subplot

            for curvename in kwargs['style'][dataname]:
                
                pos = list(kwargs['style'][dataname].keys()).index(curvename)
                # build a curve axe
                self.axes[dataname][curvename] = self.subplotd[dataname].twinx()
                self.axes[dataname][curvename].set_ylabel(curvename)

                self.axes[dataname][curvename].xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))

                self.axes[dataname][curvename].spines['right'].set_color(
                    kwargs['style'][dataname][curvename]['style'][0]
                )
                self.axes[dataname][curvename].spines['right'].set_linewidth(2)
                self.axes[dataname][curvename].spines['right'].set_position(
                    ('axes', 1+offsetrc*pos)
                )

                # join y axes
                if 'join' in kwargs:
                    if dataname in kwargs['join']:
                        if curvename in kwargs['join'][dataname]:
                            if kwargs['join'][dataname].index(curvename) != 0:
                                self.axes[dataname][curvename].get_shared_y_axes().join(
                                    self.axes[dataname][kwargs['join']
                                                        [dataname][0]]
                                )
                                
                # set the curve-ylim

                ymin=data[dataname][curvename].dropna().min()
                ymax= data[dataname][curvename].dropna().max()
                if not np.isnan([ymin,ymax]).any():
                    self.axes[dataname][curvename].set_ylim(ymin,ymax)

                # plot the curve
                if kwargs['style'][dataname][curvename]['type'] == 'scatter':
                    self.curves[dataname][curvename] = self.axes[dataname][curvename].scatter(
                        data[dataname][xcol],  # xcol
                        data[dataname][curvename],  # curvecol
                        **kwargs['style'][dataname][curvename]['style']# curvestyle
                    )
                elif kwargs['style'][dataname][curvename]['type'] == 'plot':
                    [self.curves[dataname][curvename]] = self.axes[dataname][curvename].plot(
                        data[dataname][xcol],  # xcol
                        data[dataname][curvename],  # curvecol
                        kwargs['style'][dataname][curvename]['style']# curvestyle
                    )
                else:
                    [self.curves[dataname][curvename]] = self.axes[dataname][curvename].plot(
                        data[dataname][xcol],  # xcol
                        data[dataname][curvename]  # curvecol
                    )

            lcurves=len(list(kwargs['style'][dataname].keys()))
            if lcurves*offsetrc+1>rightrc:
                rightrc=lcurves*offsetrc+1
            self.fig.subplots_adjust(
                left=0,
                right=1/rightrc
            )
            
        try:
            curve_x = curve._x
            self.mpl_connect('button_press_event',
                             lambda x: self.mousepress(x, data, xcol, _x=curve_x, **kwargs))  # 'motion_notify_event'
        except:
            None

    def mousepress(self, event, data, xcol, _x, **kwargs):
        if event.button == 3:
            for xvl in self.xvl:
                xvl.remove()
            for anno in self.anno:
                anno.remove()
            self.xvl=[]
            self.anno=[]
            self.draw()
            return
        if (not event.xdata) or (not event.ydata):
            return
        '''
        if not (isinstance(event.xdata,int) and isinstance(event.ydata,int)):
            return
        '''
        xindex0 = get_near_index(_x, event.xdata)

        if not xindex0:
            return

        x = _x[xindex0]
        y = event.ydata
        txtdict = {}
        for dataname in self.subplotd:
            # xvl
            [xmin, xmax] = self.subplotd[dataname].get_xlim()
            [ymin, ymax] = self.subplotd[dataname].get_ylim()
            self.xvl.append(
                self.subplotd[dataname].plot(
                    [x, x], [ymin, ymax], 'k:'
                )[0]
            )

            self.subplotd[dataname].set_xlim(xmin, xmax)
            self.subplotd[dataname].set_ylim(ymin, ymax)

            # x index
            if data[dataname].empty:
                continue
            curvename = list(self.curves[dataname].keys())[0]
            xindex = get_near_index(
                self.curves[dataname][curvename]._x, event.xdata)

            # txt[dataname]
            if 'txtcol' in kwargs and xindex:
                txtdict[dataname] = {}
                for colname in kwargs['txtcol'][dataname]:
                    # txtdict[dataname][colname] = None
                    if xindex <= len(data[dataname][colname]) and xindex >= 0:
                        txtdict[dataname][colname] = str(
                            data[dataname][colname].iloc[xindex])
            pass

        # annotate
        if len(txtdict):
            ftxt = ''
            for dataname in txtdict:
                ftxt = ftxt+'\n'+dataname
                for curvename in txtdict[dataname]:
                    if txtdict[dataname][curvename]:
                        ftxt = ftxt+'\n   '+curvename + \
                            ':'+txtdict[dataname][curvename]
                bbox_prop = dict(
                    boxstyle="round",
                    facecolor="yellow",
                    edgecolor="0.5",
                    alpha=1
                )
                self.anno.append(
                    event.inaxes.text(x, y, ftxt, wrap=True, bbox=bbox_prop)
                )
        else:
            None
        self.draw()

        # update anno and xvl

    def mousemove(self, event):
        pass

class imgview(QtWidgets.QGraphicsView):
    def __init__(self):
        super().__init__()
        self.img = None
        self.initui()
        self.signal()

    def initui(self):
        

        # widget
        self.gscene = QtWidgets.QGraphicsScene()
        self.clipboard=QtWidgets.QApplication.clipboard()
        self.rightmenu=QtWidgets.QMenu()

        # action
        self.actcopy=self.rightmenu.addAction('Copy')
        self.actcopy.setShortcut('Ctrl+C')
        self.actpaste=self.rightmenu.addAction('Paste')
        self.actpaste.setShortcut('Ctrl+V')

    def drawimg(self, data):
        self.img = data
        dpi = 100
        self.fig = matfig(
            figsize=(self.width()/dpi, self.height()/dpi), dpi=100)
        # self.fig=matfig()
        #self.gscene.setSceneRect(0, 0, self.width()/dpi/dpi, self.height()/dpi/dpi)
        self.canvas = matcanvas(self.fig)
        self.gscene.addWidget(self.canvas)
        self.setScene(self.gscene)
        self.subfig = self.fig.add_subplot(111)
        self.subfig.imshow(self.img)
        self.subfig.axis('off')

    def rightbuttpressed(self):
        self.rightmenu.popup(QtGui.QCursor.pos())
        #self.rightmenu.show()

    def copyimg(self):
        mimedata=QtCore.QMimeData()
        mimedata.setImageData(self.img)
        self.clipboard.setMimeData(mimedata)
        print('copy')

    def pasteimg(self):
        print('paste',type(self.clipboard.mimeData().imageData()))
        self.drawimg(self.clipboard.mimeData().imageData())
        
    
    def clear(self):
        self.img = None
        self.gscene.clear()

    def signal(self):
        self.customContextMenuRequested.connect(self.rightbuttpressed)
        self.actcopy.triggered.connect(self.copyimg)
        self.actpaste.triggered.connect(self.pasteimg)


class curveview(QtWidgets.QGraphicsView):
    def __init__(self, data=None):
        super().__init__()
        self.setObjectName("curveview")
        self.gscene = None

    def redrawgscene(self, data, xcol, xlim, **kwargs):  # data is a list of dataframe
        dpi = 100.
        self.canvas = curvecanvas(
            width=self.width()/dpi, height=self.height()/dpi, dpi=dpi)
        self.canvas.plotdata(data, xcol=xcol, xlim=xlim, **kwargs)
        self.gscene = QtWidgets.QGraphicsScene()        
 
        self.gscene.addWidget(self.canvas)
        self.setScene(self.gscene)
        # self.show()

    

class curvesuitewidget(QtWidgets.QWidget):
    def __init__(self):
        super().__init__()
        self.selectedwellname = ''
        self.initui()
        self.signal()

    def initui(self):
        # widget
        self.comboboxrborm = QtWidgets.QComboBox()
        self.comboboxrborm.addItems(['日度', '月度'])
        self.bedateline = QtWidgets.QDateEdit()
        self.bedateline.setCalendarPopup(1)
        self.endateline = QtWidgets.QDateEdit()
        self.endateline.setCalendarPopup(1)
        self.curvebuttall = QtWidgets.QPushButton('Al')
        self.curvebuttredr = QtWidgets.QPushButton('Rd')
        self.curveview = curveview()

        # layout
        self.lay = QtWidgets.QVBoxLayout()
        self.laybutt = QtWidgets.QHBoxLayout()
        # set lay
        self.setLayout(self.lay)
        self.lay.addLayout(self.laybutt)
        self.lay.addWidget(self.curveview)
        # laybutt
        self.laybutt.addWidget(self.comboboxrborm)
        self.laybutt.addWidget(self.curvebuttall)
        self.laybutt.addWidget(self.bedateline)
        self.laybutt.addWidget(self.endateline)
        self.laybutt.addWidget(self.curvebuttredr)

    def signal(self):
        self.curvebuttredr.clicked.connect(self.buttredrclicked)

    def setylim(self, xlim):
        if not self.curveview.gscene:
            return
        for dataname in self.curveview.canvas.axes:
            for curvename in self.curveview.canvas.axes[dataname]:
                dslice = self.curveview.canvas.data[dataname][curvename][(self.curveview.canvas.data[dataname][self.curveview.canvas.xcol].__ge__(pd.Timestamp(xlim[0]))) & (self.curveview.canvas.data[dataname][self.curveview.canvas.xcol].__le__(pd.Timestamp(xlim[1])))]
                if not dslice.empty:
                    ymin=dslice.dropna().min()
                    ymax=dslice.dropna().max()
                    if not np.isnan([ymin,ymax]).any():
                        self.curveview.canvas.axes[dataname][curvename].set_ylim([
                                                                                    ymin, ymax])


    def buttredrclicked(self):
        xlim=[self.bedateline.date().toPyDate(),
                self.endateline.date().toPyDate()]
        if xlim[0].__ge__(xlim[1]):
            QtWidgets.QMessageBox(
                self,
                'error',
                '日期错误'
            )
            return
        if self.curveview.gscene:
            # for anno in self.curveview.canvas.anno:
            #     anno.remove()
            # for xvl in self.curveview.canvas.xvl:
            #     xvl.remove()
            # self.curveview.canvas.xvl=[]
            # self.curveview.canvas.anno=[]
            self.curveview.canvas.subplots[0].set_xlim(xlim)
            self.setylim(xlim)
            self.curveview.canvas.draw()

# ===============importtablewidget========

class importtable(QtWidgets.QTableWidget):
    def __init__(self, **kwargs):
        super().__init__()
        # ui
        self.horizontalHeader().setVisible(0)
        #
        self.setdata(**kwargs)

    def setdata(self, **kwargs):
        self.data=None
        self.combolist=['']
        if 'combolist' in kwargs:
            self.combolist=['']+kwargs['combolist']
        if 'data' in kwargs:
            self.data=kwargs['data']  # dataframe
            self._indata()

    def _indata(self):
        if self.data.empty:
            return
        if not self.data.shape[0]+self.data.shape[1]:
            return
        self.setRowCount(self.data.shape[0]+1)
        self.setColumnCount(self.data.shape[1])

        for n in range(self.columnCount()):
            combo=QtWidgets.QComboBox()
            combo.addItems(self.combolist)
            self.setCellWidget(0, n, combo)

        for m in range(1, self.rowCount()):
            for n in range(self.columnCount()):
                item=QtWidgets.QTableWidgetItem(
                    self.data.astype('str').iloc[m-1, n])
                self.setItem(m, n, item)

        for n in range(self.columnCount()):
            txt=self.item(1, n).text()
            if txt in self.combolist:
                self.cellWidget(0, n).setCurrentText(txt)


# ==============importsuitewidget======

class importsuitewidget(QtWidgets.QWidget):
    def __init__(self, parent, **kwargs):
        super().__init__()
        self.initui()
        self.setui(**kwargs)
        self.signal(parent)

    def initui(self):
        #
        self.combodict={}
        # widgets
        self.combotable=QtWidgets.QComboBox()
        self.buttopenfile=QtWidgets.QPushButton('Open File')
        self.tablewidget=importtable()

        self.tiplabel=QtWidgets.QLabel('选择行数范围 : ')
        self.beginrowedit=QtWidgets.QLineEdit()
        self.splable=QtWidgets.QLabel(' - ')
        self.endrowdedit=QtWidgets.QLineEdit()
        self.buttimport=QtWidgets.QPushButton('Import')

        # layouts
        self.lay=QtWidgets.QVBoxLayout()
        self.laybutt=QtWidgets.QHBoxLayout()
        self.layimport=QtWidgets.QHBoxLayout()
        # laybutt
        self.laybutt.addWidget(self.combotable)
        self.laybutt.addWidget(self.buttopenfile)
        # layimport
        self.layimport.addWidget(self.tiplabel)
        self.layimport.addWidget(self.beginrowedit)
        self.layimport.addWidget(self.splable)
        self.layimport.addWidget(self.endrowdedit)
        self.layimport.addWidget(self.buttimport)
        # set lay
        self.lay.addLayout(self.laybutt)
        self.lay.addWidget(self.tablewidget)
        self.lay.addLayout(self.layimport)

        self.setLayout(self.lay)

    def setui(self, **kwargs):
        if 'combodict' in kwargs:
            self.combotable.addItems(list(kwargs['combodict'].keys()))
            self.combodict=kwargs['combodict']
        if 'currenttable' in kwargs:
            self.combotable.setCurrentText(kwargs['currenttable'])
            self.tablewidget.combolist=kwargs['combodict'][kwargs['currenttable']]

    def buttopenfileclicked(self):
        if not self.combotable.currentText:
            QtWidgets.QMessageBox(
                self, 'Error', '!! Please select a table to import !!'
            )
            return
        filename=QtWidgets.QFileDialog.getOpenFileName(
            self, caption='Open Csv', filter="Csv Files (*.*)"
        )[0]
        if not filename:
            return
        try:
            data=pd.read_csv(filename, encoding='GB2312', header=None)
        except Exception as err:
            choose=QtWidgets.QMessageBox.critical(
                self,
                'Error', str(err),
                QtWidgets.QMessageBox.Retry | QtWidgets.QMessageBox.Cancel
            )
            if choose == QtWidgets.QMessageBox.Retry:
                self.buttopenfileclicked()
                return
            else:
                return
        data.fillna(0,inplace=True)
        self.tablewidget.setdata(
            data=data,
            combolist=self.combodict[self.combotable.currentText()]
        )
        vali=QtGui.QIntValidator(1, data.shape[0]-1, parent=self)
        self.beginrowedit.setValidator(vali)
        self.endrowdedit.setValidator(vali)
        self.beginrowedit.setText('2')
        self.endrowdedit.setText(str(data.shape[0]))

        labels=['行号']
        for n in range(data.shape[0]):
            labels.append(str(n+1))
        self.tablewidget.setVerticalHeaderLabels(labels)

    def getcolumns(self):
        column={}
        for m in range(self.tablewidget.columnCount()):
            txt=self.tablewidget.cellWidget(0, m).currentText()
            if txt:
                column[txt]=m
        return column

    def getshowingdata(self, **kwargs):
        coldict=self.getcolumns()
        beginrow=int(self.beginrowedit.text())
        endrow=int(self.beginrowedit.text())

        mcol=len(kwargs['columns'])
        nrow=endrow+1-beginrow

        data=pd.DataFrame(
            [['']*mcol]*nrow,
            columns=kwargs['columns']
        )

        for n in range(beginrow, endrow+1):
            nindex=n-beginrow
            for col in coldict:
                content=self.tablewidget.item(n, coldict[col]).text()
                data[col].iloc[nindex]=content

        if 'type' in kwargs:
            if kwargs['type'] == 'df':
                return data
        return data.values.tolist()

    def updata(self, data, parent):  # wellhistiryrec
        sqlupdata='insert ignore INTO pytrol.wellhistoryrec \
            (wellid,evtdate,evt,horizon,boil,bmoist,bheight,aoil,amoist,aheight,detail) \
            VALUES ( \
            (select pytrol.welllist.wellid from pytrol.welllist where pytrol.welllist.wellname=%s), \
            STR_TO_DATE(%s,\'%%Y-%%m-%%d\'), \
            %s,%s,%s,%s,%s,%s,%s,%s,%s);'
        for i in data:
            parent.dataglobal.pmqcur.execute0(sqlupdata, i)

    def buttimportclicked(self, parent):  # winimport import butt clicked
        if not self.tablewidget.rowCount():
            return
        data=self.getshowingdata(
            columns=['井号', '日期', '事件', '层位', '前日产油','前含水', '前液面', '后日产油', '后含水', '后液面', '详情']
        )
        self.updata(data, parent)

    def getheaderlabel(self, **kwargs):
        mcol=self.tablewidget.columnCount()
        nrow=self.tablewidget.rowCount()
        hlabel=[]
        vlabel=[]
        if 'orientation' in kwargs:
            if kwargs['orientation'] == 'v':
                for n in range(nrow):
                    vlabel.append(
                        self.tablewidget.verticalHeaderItem(n).text())
                return vlabel
            elif kwargs['orientation'] == 'h':
                for m in range(1,1+mcol):
                    print(m)
                    hlabel.append(
                        self.tablewidget.horizontalHeaderItem(m).text())
                return hlabel
        else:
            for m in range(mcol):
                hlabel.append(self.tablewidget.horizontalHeaderItem(m).text())
            for n in range(nrow):
                vlabel.append(self.tablewidget.verticalHeaderItem(n).text())
            return hlabel, vlabel

    def signal(self, parent):
        self.buttopenfile.clicked.connect(
            self.buttopenfileclicked
        )
        self.buttimport.clicked.connect(
            lambda: self.buttimportclicked(parent)
        )

# ===============tablewidget===========


class tablesuitewidget(QtWidgets.QWidget):
    def __init__(self, **kwargs):
        super().__init__()
        self.setinitcol(**kwargs)
        self.initui()
        self.signal()
        self.validatornum=QtGui.QDoubleValidator(bottom=.0, decimals=2)
        self.validatornumw=QtGui.QDoubleValidator(
            bottom=.0, top=10, decimals=2)
        # self.validator
        self.validatornum.setNotation(QtGui.QDoubleValidator.StandardNotation)
        self.validatornumw.setNotation(QtGui.QDoubleValidator.StandardNotation)
        
    def initui(self):
        # widget
        self.tablebuttaddrow=QtWidgets.QPushButton('New row')
        self.tablebuttuptable=QtWidgets.QPushButton('Upload')
        self.tablebuttdelrow=QtWidgets.QPushButton('Delete row')
        self.tablebuttimport=QtWidgets.QPushButton('Import')
        self.tablebuttexport=QtWidgets.QPushButton('Export')
        self.tablewidget=QtWidgets.QTableWidget()

        # tablewidget settings
        self.tablewidget.setWordWrap(True)
        self.tablewidget.setSelectionBehavior(
            QtWidgets.QAbstractItemView.SelectRows)  # 点选中一行
        
        # layout
        self.lay=QtWidgets.QVBoxLayout()
        self.laybutt=QtWidgets.QHBoxLayout()

        # set layout
        self.setLayout(self.lay)
        self.lay.addLayout(self.laybutt)
        self.lay.addWidget(self.tablewidget)
        self.laybutt.addWidget(self.tablebuttaddrow)
        self.laybutt.addWidget(self.tablebuttdelrow)
        self.laybutt.addWidget(self.tablebuttuptable)
        self.laybutt.addWidget(self.tablebuttimport)
        self.laybutt.addWidget(self.tablebuttexport)

    def setinitcol(self, **kwargs):
        self.selectedwellname=''
        if 'columns' in kwargs:
            columns=kwargs['columns']
            mcol=len(columns)
            self.tablewidget.setColumnCount(mcol)
            self.tablewidget.setHorizontalHeaderLabels(columns)
        self.kwargs=kwargs

    def getheaderlabel(self, **kwargs):
        mcol=self.tablewidget.columnCount()
        nrow=self.tablewidget.rowCount()
        hlabel=[]
        vlabel=[]
        if 'orientation' in kwargs:
            if kwargs['orientation'] == 'v':
                for n in range(nrow):
                    vlabel.append(
                        self.tablewidget.verticalHeaderItem(n).text())
                return vlabel
            elif kwargs['orientation'] == 'h':
                for m in range(mcol):
                    hlabel.append(
                        self.tablewidget.horizontalHeaderItem(m).text())
                return hlabel
        else:
            for m in range(mcol):
                hlabel.append(self.tablewidget.horizontalHeaderItem(m).text())
            for n in range(nrow):
                vlabel.append(self.tablewidget.verticalHeaderItem(n).text())
            return hlabel, vlabel

    def filltable(self, data, **kwargs):
        self.tabdata=data
        [nrow, mcol]=self.tabdata.shape
        self.tablewidget.setRowCount(nrow)
        self.tablewidget.setColumnCount(mcol)
        if nrow == 0:
            return None

        hheader=self.getheaderlabel(orientation='h')
        combodict={}
        numcolumns=[]
        if 'combodict' in self.kwargs:
            combodict=self.kwargs['combodict']
        if 'numcolumns' in self.kwargs:
            numcolumns=self.kwargs['numcolumns']

        for m in range(mcol):
            if isinstance(self.tabdata.iloc[0][m], dt.date):  # date column
                for n in range(nrow):
                    dateedit=QtWidgets.QDateEdit(self.tabdata.iloc[n][m])
                    dateedit.setDate(self.tabdata.iloc[n][m])
                    dateedit.setDisplayFormat('yyyy-MM-dd')
                    dateedit.setCalendarPopup(True)
                    dateedit.setFrame(False)
                    self.tablewidget.setCellWidget(n, m, dateedit)
            elif hheader[m] in combodict:  # combobox column
                for n in range(nrow):
                    combox=QtWidgets.QComboBox()
                    combox.setFrame(False)
                    combox.addItems(combodict[hheader[m]])
                    combox.setCurrentText(
                        self.tabdata.astype('str').iloc[n][m])
                    self.tablewidget.setCellWidget(n, m, combox)
            elif hheader[m] in numcolumns:  # num column
                for n in range(nrow):
                    item=QtWidgets.QLineEdit()
                    item.setFrame(False)
                    if '含水' in hheader[m]:
                        item.setValidator(self.validatornumw)
                    else:
                        item.setValidator(self.validatornum)
                    item.setText(self.tabdata.astype('str').iloc[n][m])
                    self.tablewidget.setCellWidget(n, m, item)
            elif hheader[m] == '井号':
                for n in range(nrow):
                    item=QtWidgets.QLineEdit(
                        self.tabdata.astype('str').iloc[n][m])
                    item.setFrame(False)
                    self.tablewidget.setCellWidget(n, m, item)
            else:
                for n in range(nrow):
                    item=QtWidgets.QPlainTextEdit(
                        self.tabdata.astype('str').iloc[n][m])
                    self.tablewidget.setCellWidget(n, m, item)
                    item.setSizeAdjustPolicy(QtWidgets.QAbstractScrollArea.AdjustToContents)
        self.tablewidget.resizeRowsToContents()
        # current item signal
        # self.tablewidget.itemChanged.connect(
        #    lambda item: item.setForeground(QtGui.QBrush(QtGui.QColor('red'))))

    def addrow(self, **kwargs):
        self.tablewidget.setRowCount(self.tablewidget.rowCount()+1)

        mcol=self.tablewidget.columnCount()
        hheader=self.getheaderlabel(orientation='h')
        combodict={}
        numcolumns=[]
        if 'combodict' in self.kwargs:
            combodict=self.kwargs['combodict']
        if 'numcolumns' in self.kwargs:
            numcolumns=self.kwargs['numcolumns']

        for i in range(mcol):
            if hheader[i] == '日期':
                dateedit=QtWidgets.QDateEdit(dt.date.today())
                dateedit.setDisplayFormat('yyyy-MM-dd')
                dateedit.setFrame(False)
                dateedit.setCalendarPopup(True)
                self.tablewidget.setCellWidget(
                    self.tablewidget.rowCount()-1, i, dateedit)
            elif hheader[i] == '井号':
                item=QtWidgets.QLineEdit(self.selectedwellname)
                self.tablewidget.setCellWidget(
                    self.tablewidget.rowCount()-1, i, item)
            elif hheader[i] in combodict:
                combox=QtWidgets.QComboBox()
                combox.addItems(combodict[hheader[i]])
                self.tablewidget.setCellWidget(
                    self.tablewidget.rowCount()-1, i, combox)
            elif hheader[i] in numcolumns:
                item=QtWidgets.QLineEdit()
                if '含水' in hheader[i]:
                    item.setValidator(self.validatornumw)
                else:
                    item.setValidator(self.validatornum)
                self.tablewidget.setCellWidget(
                    self.tablewidget.rowCount()-1, i, item)
            else:
                item=QtWidgets.QPlainTextEdit()
                self.tablewidget.setCellWidget(
                    self.tablewidget.rowCount()-1, i, item)
        self.tablewidget.editItem(
            self.tablewidget.item(self.tablewidget.rowCount(), 0))

        # current item signal
        # self.tablewidget.itemChanged.connect(
        #    lambda item: item.setForeground(QtGui.QBrush(QtGui.QColor('red'))))
    def getselectrow(self):
        mcol=self.tablewidget.columnCount()

        rowdata=[0]*mcol
        if self.tablewidget.selectionModel().currentIndex():
            n=self.tablewidget.selectionModel().currentIndex().row()
            for m in range(mcol):
                cellwidget=self.tablewidget.cellWidget(n, m)
                if isinstance(cellwidget, QtWidgets.QDateEdit):
                    rowdata[m]=cellwidget.date().toString("yyyy-MM-dd")
                elif isinstance(cellwidget, QtWidgets.QComboBox):
                    rowdata[m]=cellwidget.currentText()
                elif isinstance(cellwidget, QtWidgets.QPlainTextEdit):
                    rowdata[m]=cellwidget.toPlainText()
                elif isinstance(cellwidget, QtWidgets.QLineEdit):
                    rowdata[m]=cellwidget.text()
            return rowdata

    def delrow(self):
        if self.tablewidget.selectionModel().currentIndex():
            n=self.tablewidget.selectionModel().currentIndex().row()
            self.tablewidget.model().removeRow(n)

    def getshowingdata(self, **kwargs):
        hheader=self.getheaderlabel(orientation='h')

        outdata=pd.DataFrame(
            [['']*self.tablewidget.columnCount()] * self.tablewidget.rowCount(),
            columns=hheader
        )
        for m in range(self.tablewidget.columnCount()):
            for n in range(self.tablewidget.rowCount()):
                cellwidget=self.tablewidget.cellWidget(n, m)
                if isinstance(cellwidget, QtWidgets.QDateEdit):
                    idate=self.tablewidget.cellWidget(n, m).date()
                    outdata.iloc[n, m]=idate.toString("yyyy-MM-dd")
                elif isinstance(cellwidget, QtWidgets.QComboBox):
                    outdata.iloc[n, m]=cellwidget.currentText()
                elif isinstance(cellwidget, QtWidgets.QPlainTextEdit):
                    outdata.iloc[n, m]=cellwidget.toPlainText()
                elif isinstance(cellwidget, QtWidgets.QLineEdit):
                    outdata.iloc[n, m]=cellwidget.text()
        if 'type' in kwargs:
            if kwargs['type'] == 'df':
                return outdata
        return outdata.values.tolist()

    def exportdata(self):
        furl=QtWidgets.QFileDialog.getSaveFileUrl(
            caption='Save',
            filter='Csv files(*.csv)'
        )[0].toLocalFile()
        if furl:
            try:
                self.getshowingdata(type='df').to_csv(
                    furl, index=0, encoding='GB2312'
                )
            except Exception as err:
                choose=QtWidgets.QMessageBox.critical(
                    self,
                    'Error', str(err),
                    QtWidgets.QMessageBox.Retry | QtWidgets.QMessageBox.Cancel
                )
                if choose == QtWidgets.QMessageBox.Retry:
                    self.exportdata()

    def signal(self):
        self.tablewidget.cellChanged.connect(
            lambda: self.tablewidget.resizeColumnsToContents())
        self.tablebuttexport.clicked.connect(
            self.exportdata)

# ===============tablebiew=============


'''
class tablemodel(QtGui.QStandardItemModel):
    def __init__(self, tabdata=None):  # tabdata is a dataframe
        super().__init__()

    def update(self, data):
        self.clear()
'''
'''
class tableview(QtWidgets.QTableView):
    def __init__(self):
        super().__init__()
        self.tabmodel = tablemodel()
        self.setModel(self.tabmodel)

    def fillmodel(self, data):
        self.tabmodel = tablemodel()
        self.setModel(self.tabmodel)
        self.setWordWrap(1)
        self.tabdata = data
        self.tabmodel.update(data)
        [nrow, ncol] = self.tabdata.shape
        header = self.tabdata.columns
        self.tabmodel.setHorizontalHeaderLabels(header)
        self.tabmodel.setRowCount(nrow)
        self.tabmodel.setColumnCount(ncol)
        self.coldate = []
        for m in range(ncol):
            if isinstance(self.tabdata.iloc[0][m], dt.date):
                self.coldate.append(m)
                for n in range(nrow):
                    dateedit = QtWidgets.QDateEdit(self.tabdata.iloc[n][m])
                    dateedit.setDisplayFormat('yyyy-MM-dd')
                    dateedit.setCalendarPopup(True)
                    # dateedit.setData(self.tabdata.iloc[n][m])
                    self.setIndexWidget(self.model().index(n, m), dateedit)
            else:
                for n in range(nrow):
                    item = QtGui.QStandardItem(
                        self.tabdata.astype('str').iloc[n][m])
                    item.setData(self.tabdata.iloc[n][m])
                    self.tabmodel.setItem(n, m, item)
        self.signal()

    def signal(self):
        self.model().dataChanged.connect(
            lambda: self.model().itemFromIndex(self.selectionModel().currentIndex()).setForeground(QtGui.QBrush(QtGui.QColor('red'))))

    def addrow(self):
        iteritems = QtGui.QStandardItem()
        self.model().insertRow(
            self.model().rowCount(), iteritems)
        for i in self.coldate:
            dateedit = QtWidgets.QDateEdit(dt.date.today())
            dateedit.setDisplayFormat('yyyy-MM-dd')
            dateedit.setCalendarPopup(True)
            self.setIndexWidget(
                self.model().index(self.model().rowCount()-1, i), dateedit)

    def delrow(self):
        if self.selectionModel().currentIndex() != None:
            n = self.selectionModel().currentIndex().row()
            items = self.model().takeRow(n)
        pass

    def uptable(self):

        pass

    def gettabledata(self):
        # outdate=
        for n in range(self.model().rowCount()):
            for m in range(self.model().columnCount()):
                print(self.model().item(m, n).data())
'''
# ===============treeview==============


class treenode:
    # tree data struct node
    def __init__(self, layer=0):
        self.content=[]
        self.childs=[]
        self.layer=layer
        self.nextlayer=self.layer+1

    def isempty(self):
        if self.content == []:
            return 1
        else:
            return 0

    def build(self, datalist):
        for i in datalist:
            self.addrecord(i)

    def indexof(self, data):
        # index of the input data
        for i in range(len(self.content)):
            if data == self.content[i]:
                return i
        return -1

    def addrecord(self, record):
        head=record[0]
        idx=self.indexof(head)
        if len(record) == 1:
            if idx == -1:
                self.content.append(head)
                self.childs.append(treenode(self.nextlayer))
        else:
            body=record[1:]
            if idx == -1:  # is a new head
                self.content.append(head)
                self.childs.append(treenode(self.nextlayer))
                self.childs[-1].addrecord(body)
            else:
                self.childs[idx].addrecord(body)

    def getchild(self, n):
        return self.childs[n]

    def itembypath(self, path):
        x=self
        for i in range(len(path), 0, -1):
            if i == 0:
                return x.getchild(path[0])
            x=x.getchild(i)


class treemodel(QtGui.QStandardItemModel):
    def __init__(self, treedata=None):  # treedata: a n-cols dataframe
        super().__init__()
        self.tree=treenode()
        if treedata != None:
            self.update(treedata)

    def update(self, treedata):
        self.clear()
        self.tree.build(treedata)
        self.iternode(self, self.tree)

    def iternode(self, parent, node):
        leng=len(node.content)
        if leng > 0:
            for i in range(leng):
                item=QtGui.QStandardItem(node.content[i])
                item.setEditable(0)
                item.setEnabled(1)
                parent.appendRow(item)
                if node.childs[i].isempty() == 0:
                    self.iternode(item, node.childs[i])


class pickwellsuitewidget(QtWidgets.QWidget):
    def __init__(self):
        super().__init__()
        self.selectedwellname=''

        self.initui()

    def initui(self):
        # widget
        self.wellnameedit=QtWidgets.QLineEdit()
        self.pickwellbutt=QtWidgets.QPushButton('pick')
        self.treeview=QtWidgets.QTreeView()
        self.treeview.setAlternatingRowColors(True)

        # layout
        self.lay=QtWidgets.QVBoxLayout()
        self.laybutt=QtWidgets.QHBoxLayout()

        # set layout
        self.setLayout(self.lay)
        self.lay.addLayout(self.laybutt)
        self.lay.addWidget(self.treeview)
        # laybutt
        self.laybutt.addWidget(self.wellnameedit)
        self.laybutt.addWidget(self.pickwellbutt)

##############################  window  ###############################


class winimportdata(QtWidgets.QDialog):
    def __init__(self, parent, info=None, **kwargs):
        super().__init__()
        self.pmqcur=actdb.pmqcur(info=info['pmq'])
        self.oracur=actdb.oracur(info=info['ora'])
        self.initui(parent, **kwargs)

    def initui(self, parent, **kwargs):
        self.setWindowTitle('Import')
        # widgets
        self.tablewidget=importsuitewidget(parent, **kwargs)
        # layouts
        self.lay=QtWidgets.QHBoxLayout()
        # lay
        self.lay.addWidget(self.tablewidget)

        self.setLayout(self.lay)
        self.show()


class winpytrolmain(QtWidgets.QMainWindow):  # pytrol main window
    def __init__(self, parent=None):
        super().__init__()
        ### global data ###
        self.info=dict(
            pmq=['127.0.0.1', 3306, 'root', '123qweasdzxc'],
            ora=['XINXI', 'XINXI', '10.76.19.33:2520/oraunix']
        )
        self.dataglobal=actdb.dataglobal()
        self.dataglobal.connectinfo.update(self.info['pmq'])
        self.initui()
        self.actnewconnection.trigger()

    def initui(self):
        self.setWindowTitle("PYTROL")
        self.dataglobal.connectedflag=False
        self.actnewconnection=QtWidgets.QAction('new connect', self)
        self.actnewconnection.triggered.connect(self.newconn)

        self.menu=self.menuBar()
        self.menu.clear()
        self.menuconnect=self.menu.addMenu('connect')
        self.menuconnect.addAction(self.actnewconnection)

        self.mdi=QtWidgets.QMdiArea(self)
        self.setCentralWidget(self.mdi)

        # self.mdi.setTabShape(0)
        self.test=QtWidgets.QAction('test', self)
        self.test.triggered.connect(self.wintest)
        self.menutest=self.menu.addMenu('test')
        self.menutest.addAction(self.test)
        self.show()

    def connui(self):
        self.setinfo2mainwin()
        self.dataglobal.connectedflag=True

        self.actnewconnection=QtWidgets.QAction('new connect', self)
        self.actnewconnection.triggered.connect(self.newconn)
        self.actcloseconnection=QtWidgets.QAction('close connect', self)
        self.actcloseconnection.triggered.connect(self.closeconn)
        self.actnewlookupwellinfo=QtWidgets.QAction('new well info', self)
        self.actnewlookupwellinfo.triggered.connect(self.newlookup_wellinfo)
        self.actdatamanager=QtWidgets.QAction('data manager', self)
        self.synchronize=QtWidgets.QAction('Synchronize')
        self.synchronize.triggered.connect(self.syndata)

        self.menu=self.menuBar()
        self.menu.clear()
        self.menuconnect=self.menu.addMenu('connect')
        self.menuconnect.addAction(self.actnewconnection)
        self.menuconnect.addAction(self.actcloseconnection)
        self.menulookup=self.menu.addMenu('lookup')
        self.menulookup.addAction(self.actnewlookupwellinfo)
        self.menudata=self.menu.addMenu('data')
        self.menudata.addAction(self.actdatamanager)
        self.menudata.addAction(self.synchronize)
    ### function ###

    def syndata(self):
        sqlgetdata='select DISTINCT \
            a.井号 as wellname, b.油田||\'_\'||b.区块||\'_\'||b.计算层位 as blockname \
            from \
            (SELECT DISTINCT 井号 from zs2000.zs2000_jhgsb_sjsj UNION \
            select DISTINCT 井号 from zs2000.zs2000_jhgsb_yjsj UNION \
            select DISTINCT 井号 FROM zs2000.jwzbb) a \
            left JOIN \
            (SELECT 油田,区块,计算层位,井号 from zs2000.zs2000_jhgsb_yjsj UNION \
            SELECT 油田,区块,计算层位,井号 from zs2000.zs2000_jhgsb_sjsj) b \
            on a.井号=b.井号'

        sqlinput='insert ignore into pytrol.{tablename} \
            ({colname}) \
            values \
            (%s)'

        sqlbelong='insert ignore into pytrol.wellbelong \
            (wellid,blockid) \
            VALUES \
            ((select pytrol.welllist.wellid from pytrol.welllist \
            where pytrol.welllist.wellname=%s), \
            (select pytrol.blocklist.blockid from pytrol.blocklist \
            WHERE pytrol.blocklist.blockname=%s))'

        sqlblock=sqlinput.format(tablename='blocklist', colname='blockname')
        sqlwell=sqlinput.format(tablename='welllist', colname='wellname')
        indata=self.dataglobal.oracur.execute(sqlgetdata).fillna('Empty')

        welllist=indata.iloc[:, 0].drop_duplicates().values.tolist()
        for i in welllist:
            self.dataglobal.pmqcur.cur.execute(sqlwell, i)
        self.dataglobal.pmqcur.conn.commit()

        blocklist=indata.iloc[:, 1].drop_duplicates().values.tolist()
        for i in blocklist:
            self.dataglobal.pmqcur.cur.execute(sqlblock, i)
        self.dataglobal.pmqcur.conn.commit()

        wellbelong=indata.values.tolist()
        for i in wellbelong:
            self.dataglobal.pmqcur.cur.execute(sqlbelong, i)
        self.dataglobal.pmqcur.conn.commit()

    def setinfo2mainwin(self):
        self.setWindowTitle('PYTROL - '+self.getstrinfo())

    def getstrinfo(self):
        info=self.dataglobal.connectinfo.info()
        return info[2]+' @ '+info[0]+':'+str(info[1])

    def newconn(self):
        if self.dataglobal.connectedflag == True:
            qm=QtWidgets.QMessageBox.information(
                self,
                "new connect",
                "a connection has been established , if establish to a new connection ?",
                QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No
            )
            if qm == QtWidgets.QMessageBox.Yes:
                self.initui()
                winconn=winnewconnection(self)
        else:
            winconn=winnewconnection(self)

    def closeconn(self):
        qm=QtWidgets.QMessageBox.warning(
            self,
            'close connection', 'confirm to close the connection:\n'+self.getstrinfo(),
            QtWidgets.QMessageBox.Ok | QtWidgets.QMessageBox.Cancel
        )
        if qm == QtWidgets.QMessageBox.Ok:
            self.initui()

    def chkconn(self):
        try:
            self.dataglobal.pmqcur.ping()

        except:
            self.dataglobal.connectedflag=False
            qm=QtWidgets.QMessageBox.warning(
                self,
                'disconnected',
                'the connection at:\n'+self.getstrinfo() +
                '\nhas lost ,if you want to establish a new connection ?',
                QtWidgets.QMessageBox.Ok | QtWidgets.QMessageBox.Cancel
            )
            if qm == QtWidgets.QMessageBox.Ok:
                self.initui()
                self.newconn()

    def newlookup_wellinfo(self):
        self.chkconn()
        if self.dataglobal.connectedflag == True:
            self.winwellinfo=subwinlookupwellinfo(self)
            self.mdi.addSubWindow(self.winwellinfo)
            self.winwellinfo.show()

    def wintest(self):
        win=wintest(self)
        win.show()


class wintest(QtWidgets.QDialog):  # a window for test
    def __init__(self, parent=None):
        super().__init__(parent)
        self.initui()

    def initui(self):
        splitter=QtWidgets.QSplitter(self)
        self.txt=QtWidgets.QTextBrowser()
        self.txt1=QtWidgets.QTextBrowser()
        splitter.addWidget(self.txt)
        splitter.addWidget(self.txt1)
        print('eeee')


class winnewconnection(QtWidgets.QDialog):  # window for connection
    def __init__(self, parent=None):
        super().__init__(parent)
        self.initui()
        self.autofill(parent)
        self.bindaction(parent)
        self.show()

    def initui(self):
        self.setWindowTitle('Connect')
        self.setModal(True)
        layout=QtWidgets.QVBoxLayout()
        gridline=QtWidgets.QGridLayout()
        gridbutton=QtWidgets.QGridLayout()
        self.setFixedSize(300, 400)
        self.host=QtWidgets.QLineEdit()
        self.port=QtWidgets.QLineEdit()
        self.user=QtWidgets.QLineEdit()
        self.word=QtWidgets.QLineEdit()

        self.host.setPlaceholderText('主机ip')
        self.port.setPlaceholderText('端口号')
        self.user.setPlaceholderText('用户名')
        self.word.setPlaceholderText('密码')

        self.host.setInputMask("000.000.000.000")
        self.port.setValidator(QtGui.QIntValidator(bottom=0))
        self.word.setEchoMode(QtWidgets.QLineEdit.Password)

        self.connect=QtWidgets.QPushButton('Connect')
        self.cancel=QtWidgets.QPushButton('Cancel')

        gridline.addWidget(self.host, 0, 1)
        gridline.addWidget(self.port, 1, 1)
        gridline.addWidget(self.user, 2, 1)
        gridline.addWidget(self.word, 3, 1)
        gridbutton.addWidget(self.connect, 4, 0)
        gridbutton.addWidget(self.cancel, 4, 1)
        layout.addLayout(gridline)
        layout.addLayout(gridbutton)
        self.setLayout(layout)

    def autofill(self, parent):
        info=parent.dataglobal.connectinfo.info()
        for i in range(len(info)):
            info[i]=str(info[i])
        self.host.setText(info[0])
        self.port.setText(info[1])
        self.user.setText(info[2])
        self.word.setText(info[3])

    def bindaction(self, parent):
        self.connect.clicked.connect(lambda: self.connect_clicked(parent))
        self.cancel.clicked.connect(self.close)

    def connect_clicked(self, parent):
        parent.dataglobal.connectinfo.update(
            [
                self.host.text(),
                int(self.port.text()),
                self.user.text(),
                self.word.text()
            ]
        )
        try:
            parent.dataglobal.pmqcur.connect(
                parent.dataglobal.connectinfo.info())
        except:
            QtWidgets.QMessageBox.critical(
                self,
                'Error', 'connect failed!',
                QtWidgets.QMessageBox.Retry
            )
            return
        parent.info['pmq']=[
            self.host.text(),
            int(self.port.text()),
            self.user.text(),
            self.word.text()
        ]
        qm=QtWidgets.QMessageBox.information(
            self,
            'connected', 'connect success!',
            QtWidgets.QMessageBox.Ok
        )
        if qm == QtWidgets.QMessageBox.Ok:
            parent.connui()
            self.close()
            del self


# subwindow for well history
class subwinlookupwellinfo(QtWidgets.QWidget):
    def __init__(self, parent):
        super().__init__()
        self.initui(parent)
        self.selectedwellname=''
        parent.chkconn()
        self.autofill(parent)
        self.signal(parent)
        # self.typelist = self.gettypelist(parent)
        # self.horizonlist = self.gethorizonlist(parent)

    def initui(self, parent):
        # self
        self.setWindowTitle('well info lookup')

        # suitewidget
        self.pickwellwidget=pickwellsuitewidget()
        cellwidget='QtWidgets.QPlainTextEdit'
        pattern=[
            ['井号', 'QtWidgets.QLineEdit', ''],
            ['日期', 'QtWidgets.QDateEdit', ''],
            ['事件分类', 'QtWidgets.QCombobox', ''],
            ['事件描述', 'QtWidgets.QLineEdit', ''],
            ['层位', 'QtWidgets.QLineEdit', ''],
            ['前日产油', 'QtWidgets.QLineEdit', ''],
            ['前含水', 'QtWidgets.QLineEdit', ''],
            ['前液面', 'QtWidgets.QLineEdit', ''],
            ['后日产油', 'QtWidgets.QLineEdit', ''],
            ['后含水', 'QtWidgets.QLineEdit', ''],
            ['后液面', 'QtWidgets.QLineEdit', ''],
            ['详情', 'QtWidgets.QPlainTextEdit', '']
        ]
        self.tablewidget=tablesuitewidget()
        self.tablewidget.setinitcol(
            columns=['井号', '日期', '事件', '层位', '前日产油',
                     '前含水', '前液面', '后日产油', '后含水', '后液面', '详情'],
            numcolumns=['前日产油', '前含水', '前液面', '后日产油', '后含水', '后液面']
        )

        self.curvewidget=curvesuitewidget()
        self.wellborewidget=wellboresuitewidget()

        # splitter

        self.laysplitter=QtWidgets.QHBoxLayout()
        self.setLayout(self.laysplitter)
        self.splitter=QtWidgets.QSplitter(QtCore.Qt.Horizontal)

        self.splittermid=QtWidgets.QSplitter(QtCore.Qt.Vertical)
        self.laysplitter.addWidget(self.splitter)

        # set splitter

        self.splitter.addWidget(self.pickwellwidget)
        self.splitter.addWidget(self.splittermid)
        self.splitter.addWidget(self.wellborewidget)
        self.splitter.setSizes([500, 500, 500])

        # splitter mid
        self.splittermid.addWidget(self.tablewidget)
        self.splittermid.addWidget(self.curvewidget)

    def filltree(self, parent):
        # fill the tree treemod
        sql="select pytrol.blocklist.blockname,pytrol.welllist.wellname \
        from pytrol.wellbelong \
        LEFT JOIN pytrol.blocklist \
        on pytrol.wellbelong.blockid=pytrol.blocklist.blockid \
        LEFT JOIN pytrol.welllist \
        on pytrol.wellbelong.wellid=pytrol.welllist.wellid"
        self.blockwelldata=parent.dataglobal.pmqcur.execute(sql)
        self.treemod=treemodel(self.blockwelldata.values.tolist())
        self.pickwellwidget.treeview.setModel(self.treemod)

    def signal(self, parent):
        # pickwellsuitewidget
        self.pickwellwidget.treeview.clicked.connect(self.treeviewclicked)
        self.pickwellwidget.pickwellbutt.clicked.connect(
            lambda: self.pickbuttclicked(parent))
        self.pickwellwidget.treeview.doubleClicked.connect(
            lambda: self.treeviewdoubleClicked(parent))

        # tablesuitewidget
        self.tablewidget.tablebuttuptable.clicked.connect(
            lambda: self.updata(self.tablewidget.getshowingdata(), parent))
        self.tablewidget.tablebuttaddrow.clicked.connect(
            lambda: self.addrowbuttclicked(parent))
        self.tablewidget.tablebuttdelrow.clicked.connect(
            lambda: self.delrowbuttclicked(parent))
        self.tablewidget.tablebuttimport.clicked.connect(
            lambda: self.buttimportclicked(parent))

        # curvesuitewidget
        self.curvewidget.curvebuttall.clicked.connect(
            lambda: self.curvebuttallclicked(
                parent=parent,
                rborm=self.curvewidget.comboboxrborm.currentText()
            )
        )

        # wellboresuitewidget
        self.wellborewidget.delimgbutt.clicked.connect(
            lambda: self.delimgbuttclicked(parent))
        self.wellborewidget.addwellborewin.upbutt.clicked.connect(
            lambda: self.upbuttclicked(parent))
        self.wellborewidget.choosebox.currentTextChanged.connect(
            lambda: self.dateboxchanged(parent))

    def buttimportclicked(self, parent):
        combodict={
            '井史': ['井号', '日期', '事件', '层位', '前日产油',
                   '前含水', '前液面', '后日产油', '后含水', '后液面', '详情']
        }
        current='井史'
        self.winimport=winimportdata(
            parent=parent,
            info=parent.info,
            combodict=combodict,
            currenttable=current
        )

    def addrowbuttclicked(self, parent):
        self.tablewidget.addrow()

    def delrowbuttclicked(self, parent):
        data=[self.tablewidget.getselectrow()[0:2]]
        for i in data:
            if i[0] == 0 or i[1] == 0:
                i[0]='NaN'
                i[1]='NaN'
        self.deldata_name_date(
            tablename='pytrol.wellhistoryrec', colname=['wellname', 'evtdate'], data=data, parent=parent)
        self.tablewidget.delrow()

    def dateboxchanged(self, parent):
        date=self.wellborewidget.choosebox.currentText()
        if date:
            wn=self.wellborewidget.selectedwellname
            imgdata=self.getwellboreimgdata(
                key=[wn, date], flag='img', parent=parent)
            if imgdata:
                self.wellborewidget.drawimg(imgdata)
            else:
                self.wellborewidget.img.clear()
        else:
            self.wellborewidget.img.clear()

    def getwellboreimgdata(self, key, flag, parent):  # wellborewidget
        sqldate='select pytrol.wellboreimg.imgdate from pytrol.wellboreimg \
            where pytrol.wellboreimg.wellid=( \
            select pytrol.welllist.wellid from pytrol.welllist \
            where pytrol.welllist.wellname=%s) \
            order by pytrol.wellboreimg.imgdate desc'
        sqlimg='select pytrol.wellboreimg.img from pytrol.wellboreimg \
            where pytrol.wellboreimg.wellid=( \
            select pytrol.welllist.wellid from pytrol.welllist \
            where pytrol.welllist.wellname=%s \
            ) and pytrol.wellboreimg.imgdate=str_to_date(%s,\'%%Y-%%m-%%d\')'
        if flag == 'date':
            parent.dataglobal.pmqcur.cur.execute(sqldate, [key])
            out=parent.dataglobal.pmqcur.cur.fetchall()
            return out
        elif flag == 'img':
            parent.dataglobal.pmqcur.cur.execute(sqlimg, key)
            alldata=parent.dataglobal.pmqcur.cur.fetchall()
            img=alldata[0][0]
            if img:
                return img
            else:
                return None
            

    def upbuttclicked(self, parent):  # wellborewidget
        data=self.wellborewidget.addwellborewin.getalldata()
        if not data:
            return
        sql='insert ignore into pytrol.wellboreimg \
            values ( \
            (select pytrol.welllist.wellid from pytrol.welllist where pytrol.welllist.wellname=%s), \
            str_to_date(%s,\'%%Y-%%m-%%d\'), \
            %s, \
            %s)'
        parent.dataglobal.pmqcur.execute0(sql, data)
        # wellborewidget
        date=self.getwellboreimgdata(
            key=self.selectedwellname, flag='date', parent=parent)
        self.wellborewidget.setdate(self.selectedwellname, date)

    def updata(self, data, parent):  # wellhistiryrec
        sqlupdata='insert ignore INTO pytrol.wellhistoryrec \
            (wellid,evtdate,evt,horizon,boil,bmoist,bheight,aoil,amoist,aheight,detail) \
            VALUES ( \
            (select pytrol.welllist.wellid from pytrol.welllist where pytrol.welllist.wellname=%s), \
            STR_TO_DATE(%s,\'%%Y-%%m-%%d\'), \
            %s,%s,%s,%s,%s,%s,%s,%s,%s);'
        for i in data:
            parent.dataglobal.pmqcur.execute0(sqlupdata, i)

    # data is a df with primary keys
    def deldata_name_date(self, tablename, colname, data, parent):
        sql='delete from {tabn} \
            where wellid=(select wellid from pytrol.welllist where wellname=%s) \
            and {tabn}.{datecolname}=str_to_date(%s,\'%%Y-%%m-%%d\') '
        sql=sql.format(tabn=tablename, datecolname=colname[1])
        for i in data:
            parent.dataglobal.pmqcur.execute0(sql, i)

    def delimgbuttclicked(self, parent):
        data=[self.wellborewidget.currentdata()]
        self.deldata_name_date(
            tablename='pytrol.wellboreimg', colname=['wellname', 'imgdate'], data=data, parent=parent)
        date=self.getwellboreimgdata(
            key=self.selectedwellname, flag='date', parent=parent)
        self.wellborewidget.setdate(self.selectedwellname, date)

    def pickbuttclicked(self, parent):
        if self.pickwellwidget.wellnameedit.text():
            
            # self
            self.selectedwellname=self.pickwellwidget.wellnameedit.text()
            self.setWindowTitle('PYTROL - '+self.selectedwellname)

            # tablesuitewidget
            tabledate=self.getwellhistoryrec(
                self.selectedwellname, parent)
            self.tablewidget.selectedwellname=self.selectedwellname
            self.tablewidget.filltable(
                tabledate)

            # wellboresuitewidget
            date=self.getwellboreimgdata(
                key=self.selectedwellname, flag='date', parent=parent)
            self.wellborewidget.setdate(self.selectedwellname, date)

    def gettypelist(self, parent=None):
        sql='select pytrol.typelist.type from pytrol.typelist'
        return parent.dataglobal.pmqcur.execute(sql).iloc[:, 0].values.tolist()

    def gethorizonlist(self, parent=None):
        sql='select pytrol.horizonlist.horizon from pytrol.horizonlist'
        return parent.dataglobal.pmqcur.execute(sql).iloc[:, 0].values.tolist()

    def treeviewdoubleClicked(self, parent=None):
        # self
        self.selectedwellname=self.pickwellwidget.treeview.selectedIndexes()[
            0].data()
        self.setWindowTitle('well info lookup - '+self.selectedwellname)

        # tablesuitewidget
        tabledate=self.getwellhistoryrec([self.selectedwellname], parent)
        self.tablewidget.selectedwellname=self.selectedwellname
        self.tablewidget.filltable(tabledate)

        # curvesuitewidget
        # curvedatelist = self.getrbyjsj([self.selectedwellname], parent)
        # self.drawcurve([self.selectedwellname], parent)

        # wellboresuitewidget
        date=self.getwellboreimgdata(
            key=self.selectedwellname, flag='date', parent=parent)
        self.wellborewidget.setdate(self.selectedwellname, date)

    def curvebuttallclicked(self, parent, rborm, **kwargs):

        rbyjsjls={
            '日产液': {'type': 'plot', 'style': 'g-'},
            '含水': {'type': 'plot', 'style': 'b-'},
            '日产油': {'type': 'plot', 'style': 'r-'},
        }
        rbsjsjls={
            '干线压力': {'type': 'plot', 'style': 'k:'},
            '套压': {'type': 'plot', 'style': 'c-'},
            '油压': {'type': 'plot', 'style': 'm-'},
            '日注水量': {'type': 'plot', 'style': 'b-'}
        }
        myjsjls={
            '动液面': {'type': 'plot', 'style': 'k:_'},
            '日产液': {'type': 'plot', 'style': 'g-'},
            '含水': {'type': 'plot', 'style': 'b-'},
            '日产油': {'type': 'plot', 'style': 'r-'},
        }
        msjsjls={
            '干线压力': {'type': 'plot', 'style': 'k:'},
            '套压': {'type': 'plot', 'style': 'c-'},
            '油压': {'type': 'plot', 'style': 'm-'},
            '日注水量': {'type': 'plot', 'style': 'b-'}
        }

        xcol='日期'
        datab={}
        style={}
        join={}
        txtcol={}

        rbyjsjtitle='日报油井曲线'
        rbsjsjtitle='日报水井曲线'
        myjsjtitle='月报油井曲线'
        msjsjtitle='月报水井曲线'

        if rborm == '日度':
            tp0=time.clock()
            datab[self.selectedwellname+rbyjsjtitle]=self.getrbyjsj(
                self.selectedwellname, parent).replace(0, NaN)
            datab[self.selectedwellname+rbsjsjtitle]=self.getrbsjsj(
                self.selectedwellname, parent).replace(0, NaN)
            tp1=time.clock()
            print('oraexec tp',tp1-tp0)
            style[self.selectedwellname+rbyjsjtitle]=rbyjsjls
            style[self.selectedwellname+rbsjsjtitle]=rbsjsjls

            join[self.selectedwellname+rbsjsjtitle]=['干线压力', '油压', '套压']

            txtcol[self.selectedwellname +
                   rbyjsjtitle]=['日期', '日产液', '日产油', '含水', '备注']
            txtcol[self.selectedwellname +
                   rbsjsjtitle]=['日期', '日注水量', '干线压力', '油压', '套压', '备注']

        elif rborm == '月度':
            datab[self.selectedwellname+myjsjtitle]=self.getmyjsj(
                self.selectedwellname, parent).replace(0, NaN)
            datab[self.selectedwellname + \
                myjsjtitle]['日期']=datab[self.selectedwellname+myjsjtitle]['日期']*100

            datab[self.selectedwellname+msjsjtitle]=self.getmsjsj(
                self.selectedwellname, parent).replace(0, NaN)
            datab[self.selectedwellname + \
                msjsjtitle]['日期']=datab[self.selectedwellname+msjsjtitle]['日期']*100

            style[self.selectedwellname+myjsjtitle]=myjsjls
            style[self.selectedwellname+msjsjtitle]=msjsjls
            join[self.selectedwellname+msjsjtitle]=['干线压力', '油压', '套压']

            txtcol[self.selectedwellname+myjsjtitle]=['日期',
                                                        '日产液', '日产油', '含水', '动液面', '备注']
            txtcol[self.selectedwellname +
                   msjsjtitle]=['日期', '日注水量', '干线压力', '油压', '套压', '备注']

        self.curvewidget.datalist=datab

        [maxdate, mindate]=[dt.date.min, dt.date.max]
        emptyflag=0
        for name in datab:
            if not datab[name].empty:
                emptyflag=1
                # 月报日期格式处理
                if name == self.selectedwellname+myjsjtitle or name == self.selectedwellname+msjsjtitle:
                    def strptime(x):
                        y=dt.datetime.strptime(x, '%Y%m')
                        return y
                    datab[name]['日期']=datab[name]['日期'].astype('int').astype('str').apply(
                        lambda x: strptime(x)
                    )
                datab[name]=datab[name].sort_values(by=xcol, ascending=True)

                # get min maxdate
                if datab[name][xcol].max().__gt__(maxdate):
                    maxdate=datab[name][xcol].max()
                if datab[name][xcol].min().__lt__(mindate):
                    mindate=datab[name][xcol].min()
        if not emptyflag:
            return
        # set datelinewidgets
        self.curvewidget.bedateline.setDate(mindate)
        self.curvewidget.endateline.setDate(maxdate)

        delt=dt.timedelta(weeks=4)

        # draw curve
        tp0=time.clock()
        self.curvewidget.curveview.redrawgscene(
            datab, xcol=xcol, xlim=[mindate-delt, maxdate+delt],
            style=style,
            join=join,
            txtcol=txtcol
        )
        tp1=time.clock()
        print('drawp',tp1-tp0)

    def treeviewclicked(self, parent=None):
        self.pickwellwidget.wellnameedit.setText(
            self.pickwellwidget.treeview.selectedIndexes()[0].data())
        pass

    def autofill(self, parent=None):
        # fill the tree treemod
        sql="select pytrol.blocklist.blockname,pytrol.welllist.wellname \
            from pytrol.wellbelong \
            LEFT JOIN pytrol.blocklist \
            on pytrol.wellbelong.blockid=pytrol.blocklist.blockid \
            LEFT JOIN pytrol.welllist \
            on pytrol.wellbelong.wellid=pytrol.welllist.wellid"
        blockwelldata=parent.dataglobal.pmqcur.execute(sql)
        self.treemod=treemodel(blockwelldata.values.tolist())
        self.pickwellwidget.treeview.setModel(self.treemod)

    def getwellhistoryrec(self, wn, parent):
        '''
        sqlwellhistroyrec = "select \
            pytrol.welllist.wellname, \
            pytrol.wellhistoryrec.evtdate, \
            pytrol.typelist.type, \
            pytrol.wellhistoryrec.evt, \
            pytrol.horizonlist.horizon, \
            pytrol.wellhistoryrec.boil, \
            pytrol.wellhistoryrec.bmoist, \
            pytrol.wellhistoryrec.bheight, \
            pytrol.wellhistoryrec.aoil, \
            pytrol.wellhistoryrec.amoist, \
            pytrol.wellhistoryrec.aheight, \
            pytrol.wellhistoryrec.detail \
            from pytrol.wellhistoryrec \
            left join pytrol.welllist \
            on pytrol.welllist.wellid=pytrol.wellhistoryrec.wellid \
            left join pytrol.horizonlist \
            on pytrol.horizonlist.hid=pytrol.wellhistoryrec.hid \
            left join pytrol.typelist \
            on pytrol.typelist.typeid=pytrol.wellhistoryrec.typeid \
            where pytrol.welllist.wellname=%s \
            order by pytrol.wellhistoryrec.evtdate asc"
        '''
        sqlwellhistroyrec="select \
            pytrol.welllist.wellname, \
            pytrol.wellhistoryrec.evtdate, \
            pytrol.wellhistoryrec.evt, \
            pytrol.wellhistoryrec.horizon, \
            pytrol.wellhistoryrec.boil, \
            pytrol.wellhistoryrec.bmoist, \
            pytrol.wellhistoryrec.bheight, \
            pytrol.wellhistoryrec.aoil, \
            pytrol.wellhistoryrec.amoist, \
            pytrol.wellhistoryrec.aheight, \
            pytrol.wellhistoryrec.detail \
            from pytrol.wellhistoryrec \
            left join pytrol.welllist \
            on pytrol.welllist.wellid=pytrol.wellhistoryrec.wellid \
            where pytrol.welllist.wellname=%s \
            order by pytrol.wellhistoryrec.evtdate asc"
        return parent.dataglobal.pmqcur.execute(sqlwellhistroyrec, wn)

    def getrbyjsj(self, wn, parent):
        sql="select 井号,RQ as 日期,日产液,日产油,地层含水 as 含水, 备注 \
            from RB_YJSJ \
            WHERE 井号=:cc"

        return parent.dataglobal.oracur.execute(sql, {'cc': wn})

    def getrbsjsj(self, wn, parent):
        sql="select 井号,RQ as 日期,油压,套压,干线压力,日注入量 as 日注水量,备注 \
            from RB_SJSJ \
            WHERE 井号=:cc"
        return parent.dataglobal.oracur.execute(sql, {'cc': wn})

    def getmyjsj(self, wn, parent):
        sql="select 井号,日期,日产水+日产油 as 日产液,日产油,含水比 as 含水,动液面,生产简况 as 备注\
            from M_YJSJ \
            WHERE 井号=:cc"
        return parent.dataglobal.oracur.execute(sql, {'cc': wn})

    def getmsjsj(self, wn, parent):
        sql="select 井号,日期,油压,套压,干线压力,日注水量,备注 \
            from M_SJSJ \
            WHERE 井号=:cc"
        return parent.dataglobal.oracur.execute(sql, {'cc': wn})

    def drawcurve(self, wn, parent, **kwargs):
        datalist=self.getrbyjsj(wn, parent)
        self.curvewidget.curveview.redrawgscene(datalist, **kwargs)


'''
class subwindatamanager(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.initui()

        self.dbnames, self.tablenames = parent.dataglobal.getdbtable()
        self.changeface(self.laytable, 0)

    def initui(self):
        self.setWindowTitle('data manager')

        line = QtWidgets.QFrame()
        line.setFrameShape(QtWidgets.QFrame.HLine)
        line.setFrameShadow(QtWidgets.QFrame.Sunken)
        self.nextbutt = QtWidgets.QPushButton()
        self.previousbutt = QtWidgets.QPushButton()

        self.laybutt = QtWidgets.QHBoxLayout()
        self.laybutt.addWidget(self.previousbutt)
        self.laybutt.addWidget(self.nextbutt)

        self.nextbutt.clicked.connect(self.next)
        self.previousbutt.clicked.connect(self.previous)

        # face0 - choose table
        self.dblistview = QtWidgets.QListView()
        self.dblistview.setEditTriggers(
            QtWidgets.QAbstractItemView.EditTrigger(0))
        self.tablelistview = QtWidgets.QListView()
        self.tablelistview.setEditTriggers(
            QtWidgets.QAbstractItemView.EditTrigger(0))

        self.laytable = QtWidgets.QVBoxLayout()
        self.laytable.addWidget(self.dbbox)
        self.laytable.addWidget(self.tablebox)

        # face1 - choose file
        self.fileline = QtWidgets.QLineEdit()
        self.filebutt = QtWidgets.QPushButton()

        self.layfile = QtWidgets.QHBoxLayout()
        self.layfile.addWidget(self.fileline)
        self.layfile.addWidget(self.filebutt)

        self.filebutt.clicked.connect(self.choosefile)

        # face2 - choose column
        self.startrow = QtWidgets.QLineEdit()
        self.ccwidg = QtWidgets.QTableWidget()

        self.laycolumn = QtWidgets.QVBoxLayout()
        self.laycolumn.addWidget(self.startrow)
        self.laycolumn.addWidget(self.ccwidg)

        # face3 - insure info
        self.insureinfo = QtWidgets.QTextBrowser()

        self.layinfo = QtWidgets.QVBoxLayout()
        self.layinfo.addWidget(self.insureinfo)

        # face4 - dbreport
        self.report = QtWidgets.QTextBrowser()

        self.layreport = QtWidgets.QVBoxLayout()
        self.layreport.addWidget(self.insureinfo)

        # layout
        self.lay = QtWidgets.QVBoxLayout()
        self.lay.addLayout(self.laytable)
        self.lay.addWidget(line)
        self.lay.addLayout(self.laybutt)
        self.setLayout(self.lay)

    def previous(self):

    def next(self, parent):
        if self.faceflag == 0:  # choose table
            parent.chkconn()
            if parent.dataglobal.connectedflag == True:
                try:
                    self.db = self.dblistview.selectedIndexes()[0].data()
                    self.table = self.dbtableview.selectedIndexes()[0].data()
                except:
                    QtWidgets.QMessageBox.warning(
                        self,
                        "db & table error",
                        "Please choose the correct database and table ~ ,or unkown errors happen",
                        QtWidgets.QMessageBox.Ok
                    )
                    return None
                self.head = actdb.dataglobal.getheader(self.db, self.table)
                self.changeface(self.layfile, 1)

        elif self.faceflag == 1:  # choose file
            parent.chkconn()
            if parent.dataglobal.connectedflag == True:
                try:
                    fileurl = self.fileline.text()
                except:
                    QtWidgets.QMessageBox.warning(
                        self,
                        "fileurl error",
                        "Please choose a correct file , or unkown errors happen",
                        QtWidgets.QMessageBox.Ok
                    )
                    return None
                self.filedata = actff.getfiledata(fileurl)
                self.changeface(self.laycolumn, 2)

        elif self.faceflag == 2:  # choose column
            parent.chkconn()
            if parent.dataglobal.connectedflag == True:
                # self.inputdata=

                self.changeface(self.layinfo, 3)

        elif self.faceflag == 3:  # insure info
            parent.chkconn()
            if parent.dataglobal.connectedflag == True:

                self.changeface(self.layreport, 4)

        elif self.faceflag == 4:  # report
            parent.chkconn()
            if parent.dataglobal.connectedflag == True:

    def hidewidgets(self, w):
        for i in range(w.count()):
            w.itemAt(i).widget().hide()

    def showwidgets(self, w):
        for i in range(w.count()):
            w.itemAt(i).widget().show()

    def changeface(self, nextlay, flag):
        self.faceflag = flag

        worklay = self.lay.itemAt(0)
        self.hidewidgets(worklay)
        self.lay.removeItem(worklay)
        self.lay.insertLayout(nextlay, 0)
        self.showwidgets(nextlay)

    def autofill(self, faceflag, parent):
        if faceflag == 0:  # face choose db, table
            modeldb = QtCore.QStringListModel(self.dbnames)

            self.dblistview.setModal(modeldb)
            self.dblistview.clicked.selected(self.clickeddblistview)
        elif faceflag == 2:  # face choose column

            self.ccwidg.update(self.header, self.filedata)

    def clickeddblistview(self):
        row = self.dblistview.selectedIndexes[0].row()
        modeltable = QtCore.QStringListModel(self.tablenames[row])
        self.tablelistview.setModel(modeltable)

    def choosefile(self):
        fw = QtWidgets.QFileDialog(caption='load file')
        print(fw.getOpenFileUrl())
        self.fileline.setText(fw.getOpenFileUrl())
'''

'''
class wellboresuitewidget(QtWidgets.QWidget):
    def __init__(self):
        super().__init__()
        self.alldata=[]
        self.selectedwellname=''
        self.imgdata=[]
        [self.n, self.m]=[0, 0]
        self.initui()
        self.signal()

    def initui(self):
        # subwin
        self.addwellborewin=winaddwellbore()

        # widget
        self.choosebox=QtWidgets.QComboBox()
        self.choosebox.setEditable(0)
        # self.wellboreimg = QtWidgets.QGraphicsView()
        self.img=imgview()

        self.addimgbutt=QtWidgets.QPushButton('Add')
        self.delimgbutt=QtWidgets.QPushButton('Del')
        self.filedialog=QtWidgets.QFileDialog()
        # layout
        self.lay=QtWidgets.QVBoxLayout()
        self.laybutt=QtWidgets.QHBoxLayout()
        # set layout
        self.setLayout(self.lay)
        self.lay.addWidget(self.choosebox)
        # self.lay.addWidget(self.wellboreimg)
        self.lay.addWidget(self.img)
        self.lay.addLayout(self.laybutt)
        self.laybutt.addWidget(self.addimgbutt)
        self.laybutt.addWidget(self.delimgbutt)

    def drawimg(self, data):
        self.img.drawimg(data)

    def setdate(self, wellname, data):
        self.selectedwellname=wellname
        self.choosebox.clear()
        for i in range(len(data)):
            self.choosebox.addItem(data[i][0].strftime("%Y-%m-%d"))

    def setdata(self, data):  # data is a 4cols dataframe
        self.alldata=data
        self.imgdata=[]
        self.choosebox.clear()
        self.img.clearfig()
        [self.n, self.m]=self.getalldata().shape
        for nrow in range(self.n):
            self.choosebox.addItem(
                nrow, self.data.iloc[nrow, 1].strftime("%Y-%m-%d"))
            self.imgdata.append()

    def getalldata(self):
        return self.alldata

    def currentdata(self):
        currentdatalist=[]
        currentdatalist.append(self.selectedwellname)
        currentdatalist.append(self.choosebox.currentText())

        return currentdatalist

    def currentimg(self):
        return

    def allimg(self):
        return

    def addimgbuttclicked(self):
        self.addwellborewin.wellnameline.setText(self.selectedwellname)
        self.addwellborewin.show()

    def signal(self):
        self.addimgbutt.clicked.connect(
            lambda: self.addimgbuttclicked())
'''

class wellboresuitewidget(QtWidgets.QWidget):
    def __init__(self):
        super().__init__()
        self.alldata=[]
        self.selectedwellname=''
        self.imgdata=[]
        [self.n, self.m]=[0, 0]
        self.initui()
        self.signal()

    def initui(self):
        self.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        # subwin
        self.addwellborewin=winaddwellbore()

        # widget
        self.clipboard=QtWidgets.QApplication.clipboard()
        self.rightmenu=QtWidgets.QMenu()
        self.choosebox=QtWidgets.QComboBox()
        self.choosebox.setEditable(0)
        # self.wellboreimg = QtWidgets.QGraphicsView()
        self.img= QtWidgets.QLabel()
        self.img.setFrameStyle(1)
        self.img.setAlignment(QtCore.Qt.AlignCenter)

        self.addimgbutt=QtWidgets.QPushButton('Add')
        self.delimgbutt=QtWidgets.QPushButton('Del')
        self.filedialog=QtWidgets.QFileDialog()

        # action
        self.actcopy=self.rightmenu.addAction('Copy')
        self.actcopy.setShortcut('Ctrl+C')

        # layout
        self.lay=QtWidgets.QVBoxLayout()
        self.laybutt=QtWidgets.QHBoxLayout()
        # set layout
        self.setLayout(self.lay)
        self.lay.addWidget(self.choosebox)
        # self.lay.addWidget(self.wellboreimg)
        self.lay.addWidget(self.img)
        self.lay.addLayout(self.laybutt)
        self.laybutt.addWidget(self.addimgbutt)
        self.laybutt.addWidget(self.delimgbutt)

    def drawimg(self, data):
        self.imgdata=data
        img=QtGui.QImage.fromData(data)
        pix=QtGui.QPixmap.fromImage(img)
        pixs=pix.scaled(
            self.img.width(),self.img.height(),
            QtCore.Qt.KeepAspectRatio,QtCore.Qt.FastTransformation
        )
        self.img.setPixmap(pixs)

    def setdate(self, wellname, data):
        self.selectedwellname=wellname
        self.choosebox.clear()
        for i in range(len(data)):
            self.choosebox.addItem(data[i][0].strftime("%Y-%m-%d"))

    def setdata(self, data):  # data is a 4cols dataframe
        self.alldata=data
        self.choosebox.clear()
        self.img.clearfig()
        [self.n, self.m]=self.getalldata().shape
        for nrow in range(self.n):
            self.choosebox.addItem(
                nrow, self.data.iloc[nrow, 1].strftime("%Y-%m-%d"))
            self.imgdata.append()

    def getalldata(self):
        return self.alldata

    def currentdata(self):
        currentdatalist=[]
        currentdatalist.append(self.selectedwellname)
        currentdatalist.append(self.choosebox.currentText())
        return currentdatalist

    def currentimg(self):
        return

    def allimg(self):
        return

    def addimgbuttclicked(self):
        self.addwellborewin.wellnameline.setText(self.selectedwellname)
        self.addwellborewin.show()

    def rightbuttpressed(self):
        self.rightmenu.popup(QtGui.QCursor.pos())

    def copyimg(self):
        if self.imgdata:
            img=QtGui.QImage()
            img.loadFromData(self.imgdata)
            self.clipboard.setImage(img)

    def signal(self):
        self.addimgbutt.clicked.connect(
            lambda: self.addimgbuttclicked())
        self.customContextMenuRequested.connect(self.rightbuttpressed)
        self.actcopy.triggered.connect(self.copyimg)

class pixlabel(QtWidgets.QLabel):
    def __init__(self):
        super().__init__()
    def resizeEvent(self):
        pix=self.pixmap()
        if pix:
            pixs=pix.scaled(
                self.width(),
                __self__.height(),
                QtCore.Qt.KeepAspectRatio,QtCore.Qt.FastTransformation
            )
            self.setPixmap(pixs)
        return super().resizeEvent()

class winaddwellbore(QtWidgets.QDialog):
    def __init__(self):
        super().__init__()
        self.imgdata=None
        self.initui()
        self.signal()

    def initui(self):
        self.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        # self.setModal(True)
        self.setBaseSize(500, 600)
        self.setWindowTitle('Add wellbore')
        # widget
        self.wellnameline=QtWidgets.QLineEdit()
        self.dateedit=QtWidgets.QDateEdit(dt.date.today())
        self.statusedit=QtWidgets.QLineEdit()
        self.dateedit.setCalendarPopup(1)
        self.openbutt=QtWidgets.QPushButton('Open')
        self.filedialog=QtWidgets.QFileDialog()
        self.upbutt=QtWidgets.QPushButton('Up')
        self.wellborelabel=QtWidgets.QLabel()
        self.wellborelabel.setFrameStyle(1)
        self.wellborelabel.setAlignment(QtCore.Qt.AlignCenter)
        self.clipboard=QtWidgets.QApplication.clipboard()
        self.rightmenu=QtWidgets.QMenu()

        # action
        self.actcopy=self.rightmenu.addAction('Copy')
        self.actcopy.setShortcut('Ctrl+C')
        self.actpaste=self.rightmenu.addAction('Paste')
        self.actpaste.setShortcut('Ctrl+V')

        # layout
        self.lay=QtWidgets.QHBoxLayout()
        self.pixsplitter=QtWidgets.QSplitter(QtCore.Qt.Horizontal)
        self.laydo=QtWidgets.QVBoxLayout()
        # set layout
        self.do=QtWidgets.QWidget()
        self.do.setLayout(self.laydo)

        self.setLayout(self.lay)
        self.lay.addWidget(self.pixsplitter)

        self.pixsplitter.addWidget(self.do)
        self.pixsplitter.addWidget(self.wellborelabel)

        self.laydo.addWidget(self.wellnameline)
        self.laydo.addWidget(self.dateedit)
        self.laydo.addWidget(self.openbutt)
        self.laydo.addWidget(self.upbutt)
        
        self.wellborelabel.setMinimumSize(300,200)
        

    def rightbuttpressed(self):
        self.rightmenu.popup(QtGui.QCursor.pos())
        #self.rightmenu.show()

    def copyimg(self):
        if self.imgdata:
            img=QtGui.QImage()
            img.loadFromData(self.imgdata)
            self.clipboard.setImage(img)

    def pasteimg(self):
        img=self.clipboard.image()
        if not img:
            return
        pix=QtGui.QPixmap().fromImage(img)
        pixs=pix.scaled(
            self.width(),self.height(),
            QtCore.Qt.KeepAspectRatio,QtCore.Qt.FastTransformation
        )
        self.wellborelabel.setPixmap(pixs)
        dbyte=QtCore.QByteArray()
        buffer=QtCore.QBuffer(dbyte)
        img.save(buffer,'jpg')
        self.imgdata=dbyte.data()


    def signal(self):
        self.openbutt.clicked.connect(self.openbuttclicked)
        self.customContextMenuRequested.connect(self.rightbuttpressed)
        self.actcopy.triggered.connect(self.copyimg)
        self.actpaste.triggered.connect(self.pasteimg)
        

    def pixresize(self):
        pix=self.wellborelabel.pixmap()
        if pix:
            w=self.wellborelabel.width()
            h=self.wellborelabel.height()
            pixs=pix.scaled(
                w,h,QtCore.Qt.KeepAspectRatio,QtCore.Qt.FastTransformation
            )
            self.wellborelabel.setPixmap(pixs)

    def pixtobyte(self,pix):
        img=pix.toImage()
        dbyte=QtCore.QByteArray()
        buffer=QtCore.QBuffer(dbyte)
        img.save(buffer,'jpg')
        return dbyte.data()

    def openbuttclicked(self):
        filepath=self.filedialog.getOpenFileName(
            caption="Open Image",
            filter='Image file(*.*)'
        )[0]
        if filepath != '':
            try:
                pix=QtGui.QPixmap()
                pix.load(filepath)
                self.imgdata=self.pixtobyte(pix)
                pixs=pix.scaled(
                    self.wellborelabel.width(),
                    self.wellborelabel.height(),
                    QtCore.Qt.KeepAspectRatio,QtCore.Qt.FastTransformation
                )
                self.wellborelabel.setPixmap(pixs)
            except Exception as err:
                QtWidgets.QMessageBox.critical(
                    self,
                    '打开错误',str(err)
                )

    def getalldata(self):
        wellname=self.wellnameline.text()
        status=self.statusedit.text()
        date=self.dateedit.date().toString('yyyy-MM-dd')

        return [wellname, date, status, self.imgdata]
